﻿using HZH_Controls;
using NDK.Module.Model;
using Platform.DB;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;


namespace NDK.DB
{
    public static class MySQLDBHelper
    {
        #region 字段属性

        private const string _productDataTableName = "ProductMeasureData";
        private const string _systemStateDataTableName = "SystemStateData";
        private const string _systemNGRecordTableName = "NGRecordData";
        private const string _systemNGfaiCount = "NGFaiCount";
        private static string productType;
        #endregion

        #region 初始化

        public static void DataBaseIni(string str, List<MeasureSize> measureSizes)
        {
            try
            {
                CreatDB(str);
                CreatProductDataTable();
                CreatProductDataColumn(measureSizes);
                CreatSystemRunningStateTable();
                CreateReMeasureRecordDataTable();
                CreateNGCountTable(measureSizes);
            }
            catch (Exception ex)
            {
                NDK.Module.Model.Common.AppendUiLog.Log($"加载mysql数据库异常！异常信息为：{ex.Message}");
            }

        }

        #endregion

        #region 数据库链表创建

        /// <summary>
        /// 创建生产数据表和固定列
        /// </summary>
        private static void CreatProductDataTable()
        {
            string tablename = _productDataTableName + "_" + Common.CurProType;
            string createSQL = "CREATE TABLE IF NOT EXISTS " + tablename +
                               "(id INT AUTO_INCREMENT PRIMARY KEY," +
                               " datatime datetime NOT NULL," +
                               " sn varchar(50) NOT NULL," +
                               " checkresult varchar(50) NOT NULL," +
                               " ngFAI text ," +
                               " projectname varchar(50) NOT NULL," +
                               " turnnum varchar(50) NOT NULL)";

            DbAccess.ExecuteSql(createSQL);
        }

        /// <summary>
        /// 判断生产数据表中是否存在数据列，没有则添加
        /// </summary>
        public static void CreatProductDataColumn(List<MeasureSize> measureSizes)
        {
            try
            {
                string sql = $"SHOW COLUMNS FROM {_productDataTableName + "_" + Common.CurProType}";
                string[] columns = DbAccess.GetDbInfos(sql);
                foreach (MeasureSize size in measureSizes)
                {
                    if (size.Project != Common.CurProType)
                    {
                        break;
                    }
                    if (size == null) continue;
                    if (size.SizeName == "FAI-RANDOM-SAMPLE") continue;
                    if (columns != null && !columns.Contains(size.SizeName))
                    {
                        string insertSQL = $"ALTER TABLE {_productDataTableName + "_" + Common.CurProType} ADD COLUMN {size.SizeName} varchar(50);";
                        DbAccess.ExecuteSql(insertSQL);
                    }
                }
            }
            catch
            {

                
            }
        }


        /// <summary>
        /// 创建运行状态数据表
        /// </summary>
        private static void CreatSystemRunningStateTable()
        {
            string createSQL = "CREATE TABLE IF NOT EXISTS " + _systemStateDataTableName +
                               "(id INT AUTO_INCREMENT PRIMARY KEY," +
                               " datatimecurrent datetime NOT NULL," +
                               " currentstate varchar(50) NOT NULL," +
                               " datetimelast datetime NOT NULL," +
                               " laststate varchar(50) NOT NULL," +
                               " info varchar(300) NOT NULL)";

            DbAccess.ExecuteSql(createSQL);
        }

        /// <summary>
        /// 创建复投数据表
        /// </summary>
        private static void CreateReMeasureRecordDataTable()
        {
            string createSQL = "CREATE TABLE IF NOT EXISTS " + _systemNGRecordTableName +
                               "(id INT AUTO_INCREMENT PRIMARY KEY," +
                               " sn varchar(50) NOT NULL," +
                               " OKTimes INT NOT NULL," +
                               " NGTimes INT NOT NULL)";

            DbAccess.ExecuteSql(createSQL);
        }

        /// <summary>
        /// 创建NG计数表并插入一条数据(NGcount=0)
        /// </summary>
        private static void CreateNGCountTable(List<MeasureSize> measureSizes)
        {
            productType= Common.CurProType;
            string name = _systemNGfaiCount + "_" + productType; 
            string createSQL = "CREATE TABLE IF NOT EXISTS " + name +
                                "(id INT AUTO_INCREMENT PRIMARY KEY" +
                                ")";

            DbAccess.ExecuteSql(createSQL);

            string sql = $"SHOW COLUMNS FROM {name}";
            string[] columns = DbAccess.GetDbInfos(sql);
            string s1 = string.Empty;
            foreach (MeasureSize size in measureSizes)
            {
                if (size == null) continue;
                if (size.SizeName == "FAI_RANDOM_SAMPLE") continue;
                s1 += size.SizeName + ",";
                if (columns != null && !columns.Contains(size.SizeName))
                {
                    string insertSQL = $"ALTER TABLE {name} ADD COLUMN {size.SizeName} varchar(50);";
                    DbAccess.ExecuteSql(insertSQL);
                }
            }
            DataTable dt = QueryNGCount1();
            if (dt != null && dt.Rows.Count == 0)
            {
                //总数
                int length = s1.LastIndexOf(',');
                string s2 = s1.Substring(0, length);
                StringBuilder sql1 = new StringBuilder();
                sql1.Append($"insert into {name} ( {s2}");
                sql1.Append(") values (");
                for (int i = 0; i < s2.Split(',').Length; i++)
                {
                    if (i == s2.Split(',').Length - 1)
                        sql1.Append("'").Append(0).Append("'");
                    else
                        sql1.Append("'").Append(0).Append("'").Append(',');
                }
                sql1.Append(')');
                DbAccess.ExecuteSql(sql1.ToString());

                //NG数量
                sql1 = new StringBuilder();
                sql1.Append($"insert into {name} ( {s2}");
                sql1.Append(") values (");
                for (int i = 0; i < s2.Split(',').Length; i++)
                {
                    if (i == s2.Split(',').Length - 1)
                        sql1.Append("'").Append(0).Append("'");
                    else
                        sql1.Append("'").Append(0).Append("'").Append(',');
                }
                sql1.Append(')');
                DbAccess.ExecuteSql(sql1.ToString());

            }
        }

        #endregion

        #region 插入数据
        /// <summary>
        /// 插入一条产品测量数据
        /// </summary>
        /// <param name="measureProduct">产品数据</param>
        public static void InsertProductData(MeasureProduct measureProduct, string tablename)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($"insert into {_productDataTableName + "_" + tablename} ( datatime,sn,checkresult,ngFAI,projectname,turnnum");

            foreach (DataItem item in measureProduct.MeasureData)
            {
                if (item != null && !item.Name.Contains("SAMPLE"))
                {
                    sql.Append(',' + item.Name);
                }
            }

            sql.Append(") values (");
            sql.Append("'").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("'").Append(',');
            sql.Append("'").Append(measureProduct.SN).Append("'").Append(',');
            sql.Append("'").Append(measureProduct.OK_NG).Append("'").Append(',');
            if (measureProduct.NG_FAI != "")
                sql.Append("'").Append(measureProduct.NG_FAI.Replace("\\", ";")).Append("'").Append(',');
            else
                sql.Append("'").Append("").Append("'").Append(',');

            sql.Append("'").Append(NDK.Module.Model.Common.CurProType).Append("'").Append(',');
            sql.Append("'").Append(measureProduct.Position).Append("'");

            foreach (DataItem item in measureProduct.MeasureData)
            {
                if (item != null && !item.Name.Contains("SAMPLE"))
                {
                    sql.Append(",'" + item.Value.ToString("0.0000") + ":" + item.JugeResult + "'");
                }
            }
            sql.Append(')');
            DbAccess.ExecuteSql(sql.ToString());
        }

        /// <summary>
        /// 插入一条运行状态数据
        /// </summary>
        /// <param name="currentState">当前状态</param>
        /// <param name="lastState">上个状态</param>
        ///  <param name="info">报警信息</param>
        public static void InsertSystemState(string currentState, string currentTime, string lastState, string lastTime, string info)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($"insert into {_systemStateDataTableName} ( datatimecurrent,currentstate,datetimelast,laststate,info");
            sql.Append(") values (");
            sql.Append("'").Append(currentTime).Append("'").Append(',');//DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
            sql.Append("'").Append(currentState).Append("'").Append(',');
            sql.Append("'").Append(lastTime).Append("'").Append(',');
            sql.Append("'").Append(lastState).Append("'").Append(',');
            sql.Append("'").Append(info).Append("'");
            sql.Append(')');
            DbAccess.ExecuteSql(sql.ToString());
        }

        /// <summary>
        /// 插入一条复测产品数据
        /// </summary>
        /// <param name="sn">二维码</param>
        /// <param name="OKTimes">OK次数</param>
        /// <param name="NGTimes">NG次数</param>
        public static void InsertNGRecordData(string sn, int OKTimes, int NGTimes)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($"insert into {_systemNGRecordTableName} ( sn,OKTimes,NGTimes");
            sql.Append(") values (");
            sql.Append("'").Append(sn).Append("'").Append(',');
            sql.Append("'").Append(OKTimes).Append("'").Append(',');
            sql.Append("'").Append(NGTimes).Append("'");
            sql.Append(')');
            DbAccess.ExecuteSql(sql.ToString());
        }
        #endregion

        #region 查询数据
        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="dateTime"></param>
        /// <param name="num"></param>
        public static DataTable QueryProductData(DateTime startTime, DateTime endTime)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($"select checkresult,ngfai from {_productDataTableName} where datatime>='{startTime}' and datatime<='{endTime}'");
            return DbAccess.ExecuteQuery(sql.ToString());
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="dateTime"></param>
        /// <param name="num"></param>
        public static DataTable QueryProductResultData(DateTime startTime, DateTime endTime)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($"select checkresult from {_productDataTableName} where datatime>='{startTime}' and datatime<='{endTime}'");
            return DbAccess.ExecuteQuery(sql.ToString());
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="dateTime"></param>
        /// <param name="num"></param>
        public static DataTable QueryProductData(DateTime startTime, DateTime endTime, string sizename, string projectname)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($"select {sizename} from {_productDataTableName} where datatime>='{startTime}' and datatime<='{endTime}'and  projectname='{projectname}'");
            return DbAccess.ExecuteQuery(sql.ToString());
        }

        /// <summary>
        /// 查询系统状态数据
        /// </summary>
        /// <param name="dateTime"></param>
        /// <param name="num"></param>
        public static DataTable QueryStateData(DateTime startTime, DateTime endTime)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($"select * from {_systemStateDataTableName} where datatime>='{startTime}' and datatime<='{endTime}'");
            return DbAccess.ExecuteQuery(sql.ToString());
        }

        /// <summary>
        /// 查询系统状态数据
        /// </summary>
        /// <param name="dateTime"></param>
        /// <param name="num"></param>
        public static DataTable QueryLoadStateData(DateTime startTime, DateTime endTime)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($"select * from {_systemStateDataTableName} where datatime>='{startTime}' and datatime<='{endTime}' and station='load' ");
            return DbAccess.ExecuteQuery(sql.ToString());
        }

        /// <summary>
        /// 查询系统状态数据
        /// </summary>
        /// <param name="dateTime"></param>
        /// <param name="num"></param>
        public static DataTable QueryOfflineStateData(DateTime startTime, DateTime endTime)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($"select * from {_systemStateDataTableName} where datatime>='{startTime}' and datatime<='{endTime}' and station='offline' ");
            return DbAccess.ExecuteQuery(sql.ToString());
        }
        #endregion

        #region 查询数据(新)
        /// <summary>
        /// 指定数据库查询生产数据(sn)
        /// </summary>
        public static DataTable QueryProductDataSN1(DateTime startTime, DateTime endTime, string dbname)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($"select sn from {_productDataTableName}");
            return DbAccess.ExecuteQuery(sql.ToString(), dbname);
        }

        /// <summary>
        /// 指定数据表查询生产数据(checkresult,ngfai)
        /// </summary>
        public static DataTable QueryProductData1(DateTime startTime, DateTime endTime, string dbname)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($"select checkresult,ngfai from {_productDataTableName} where datatime>='{startTime}' and datatime<='{endTime}'");
            return DbAccess.ExecuteQuery(sql.ToString(), dbname);
        }

        /// <summary>
        /// 指定数据表查询数据(checkresult)
        /// </summary>
        public static DataTable QueryProductResultData1(DateTime startTime, DateTime endTime, string dbname)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($"select checkresult from {_productDataTableName} where datatime>='{startTime}' and datatime<='{endTime}'");
            return DbAccess.ExecuteQuery(sql.ToString(), dbname);
        }

        /// <summary>
        /// 指定数据表查询尺寸项数据
        /// </summary>
        public static DataTable QueryProductData1(DateTime startTime, DateTime endTime, string sizename, string dbname)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($"select {sizename} from {_productDataTableName} where datatime>='{startTime}' and datatime<='{endTime}'");
            return DbAccess.ExecuteQuery(sql.ToString(), dbname);
        }

        /// <summary>
        ///指定数据表查询状态信息
        /// </summary>
        public static DataTable QueryStateData1(DateTime startTime, DateTime endTime, string dbname)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($"select * from {_systemStateDataTableName} where datatime>='{startTime}' and datatime<='{endTime}'");
            return DbAccess.ExecuteQuery(sql.ToString(), dbname);
        }


        public static DataTable QueryStateData1()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($"select * from {_systemStateDataTableName} where currentstate='{"Alarm"}'");
            return DbAccess.ExecuteQuery(sql.ToString());
        }


        public static DataTable QueryStateData2()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($"select * from {_systemStateDataTableName}");
            return DbAccess.ExecuteQuery(sql.ToString());
        }


        /// <summary>
        /// 根据二维码查询复投数据表对应信息
        /// </summary>
        public static DataTable QueryDataBySN1(string sn)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($"select * from {_systemNGRecordTableName} where sn='{sn}'");
            return DbAccess.ExecuteQuery(sql.ToString());
        }

        /// <summary>
        /// 获取NG数据不良表格
        /// </summary>
        /// <returns></returns>
        public static DataTable QueryNGCount1()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($"select * from {_systemNGfaiCount}_{productType}");
            return DbAccess.ExecuteQuery(sql.ToString());
        }

        /// <summary>
        /// 根据二维码查询复投数据表OK/NG次数
        /// </summary>
        public static string QueryNGTimesData1(string sn)
        {
            int oktime = 0;
            int ngtime = 0;
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.Append($"select * from {_systemNGRecordTableName} where sn='{sn}'");
                DataTable dt = DbAccess.ExecuteQuery(sql.ToString());
                if (dt != null && dt.Rows.Count != 0)
                {
                    oktime = dt.Rows[0]["OKTimes"].ToInt();
                    ngtime = dt.Rows[0]["NGTimes"].ToInt();
                }
            }
            catch (Exception)
            {
                oktime = -1;
                ngtime = -1;
            }

            return oktime + ";" + ngtime;
        }

        #endregion

        #region 数据更新
        /// <summary>
        /// 复投数据表OK/NG次数更新
        /// </summary>
        public static void UpdateNGRecordData(string sn, int OKTimes, int NGTimes)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append($"update {_systemNGRecordTableName} set OKTimes");
            sql.Append(" = ");
            sql.Append(OKTimes);
            sql.Append(", NGTimes = ");
            sql.Append(NGTimes);
            sql.Append(" where  sn");
            sql.Append(" = ");
            sql.Append("'").Append(sn).Append("'");
            //sql.Append(')');
            DbAccess.ExecuteSql(sql.ToString());
        }

        /// <summary>
        /// NG尺寸项表格计数更新
        /// </summary>
        /// <param name="FaiName">尺寸名称</param>
        /// <param name="FaiCount">尺寸计数</param>
        public static void UpdateNgFaiCount(List<MeasureSize> measureSizes, double[] FaiCount, int id)
        {

            StringBuilder sql = new StringBuilder();
            string name = string.Empty;
            for (int i = 0; i < measureSizes.Count; i++)
            {
                name += $"{measureSizes[i].SizeName} = '{FaiCount[i]}',";
            }
            name = name.Substring(0, name.Length - 1);

            sql.Append($"update  {_systemNGfaiCount}_{productType} set  {name} where id ='{id}'");
            DbAccess.ExecuteSql(sql.ToString());
        }

        #endregion

        #region 创建数据库
        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="str">数据库名称</param>
        private static void CreatDB(string str)
        {
            DbAccess.GreatDB(str);
        }
        #endregion
    }
}
